package com.ucom;

/**
 * 
 */


import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.LinkedList;

/**
 * @author Tathagata
 * @author Joseph Grasser
 * 
 */
public class UcomStudentWorkloadCourseDAO {

        /**
         * 
         */
        private Connection dbConnection;
        private String query;
        private ResultSet rslt;
        private Statement stmtGetListEntries;

        public UcomStudentWorkloadCourseDAO() {
                try {
                	Class.forName("org.apache.derby.jdbc.ClientDriver");
        			dbConnection = DriverManager.getConnection(
        					"jdbc:derby://localhost:1527/UCOM");
                        stmtGetListEntries = dbConnection.createStatement(
                                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                                        ResultSet.CONCUR_READ_ONLY);

                } catch (ClassNotFoundException cnfe) {
                        System.out.println("Error in creating Database conncetion");
                        System.out.println(cnfe.getMessage());

                } catch (SQLException sqle) {
                        System.out.println("Error in creating Database conncetion");
                        System.out.println(sqle.getMessage());
                }

        }

        public LinkedList<Query6> query6() {
                LinkedList<Query6> result = new LinkedList<Query6>();
                try {
                                query = "SELECT COUNT(COMMAND) ,USER_ID" 
                                + "FROM USAGE_HISTORY"
                                + "WHERE USER_ID"
                                + "IN (SELECT USER_ID FROM TRANSCRIPT WHERE COURSE_ID IN ( SELECT COURSE_ID FROM COURSE_INFO))"
                                + "GROUP BY USER_ID";
                
                        rslt = stmtGetListEntries.executeQuery(query);
                        
                        while (rslt.next()) {
                                System.out.println("hello there");
                                System.out.print(rslt.getString(1));
                                System.out.print("\t" + rslt.getInt(2));
                                System.out.println("\t" + rslt.getInt(3));
                                result.add(new Query6(rslt.getString(1), rslt.getInt(2), rslt.getInt(3)));
                        }
                        rslt.close();
                } catch (SQLException sqle) {
                        System.out.println("Error in getting transaction details");
                        System.out.println(sqle.getMessage());
                } catch (Exception e) {
                        e.printStackTrace();
                }
                return result;
        }
}
